package com.hnkywl.sts.dao.cwgl.gsjyys;

import com.hnkywl.sts.dao.base.RegionDao;
import com.hnkywl.sts.entity.cwgl.gsjyys.Gsjyys;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Property;
import org.hibernate.criterion.Restrictions;
import org.hibernate.tool.hbm2x.StringUtils;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.hibernate.type.Type;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 * User: ccr
 * Date: 13-6-16
 * Time: 下午4:21
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class GsjyysDao extends SimpleHibernateTemplate<Gsjyys,Long> {
    public Page<Map> findPage(Long gsid,Long bmid,Integer nf,Integer yf,String x,int pageNo,int pageSize){
        Page<Map> page = new Page<Map>();
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setAutoCount(true);
        page.setOrder("desc");
        page.setOrderBy("dateCreated");
        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("gs", "gs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("bm", "bm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("createUser", "createUser", CriteriaSpecification.LEFT_JOIN)
                .createAlias("shr", "shr", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Projections.sqlProjection("nf as nf", new String[]{"nf"}, new Type[]{Hibernate.INTEGER}))
                        .add(Projections.sqlProjection("yf as yf", new String[]{"yf"}, new Type[]{Hibernate.INTEGER}))
                        .add(Projections.sqlProjection("x as x", new String[]{"x"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("gs.jc").as("gs.jc"))
//                        .add(Property.forName("gs.id").as("gs.id"))
                        .add(Property.forName("bm.jc").as("bm.jc"))
//                        .add(Property.forName("bm.id").as("bm.id"))
//                        .add(Projections.sqlProjection("gs.jc as gs.jc", new String[]{"gs.jc"}, new Type[]{Hibernate.STRING}))
//                        .add(Projections.sqlProjection("gs.id as gs.id", new String[]{"gs.id"}, new Type[]{Hibernate.STRING}))
//                        .add(Projections.sqlProjection("bm.jc as bm.jc", new String[]{"bm.jc"}, new Type[]{Hibernate.STRING}))
//                        .add(Projections.sqlProjection("bm.id as bm.id", new String[]{"bm.id"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("dateCreated").as("dateCreated"))
                        .add(Property.forName("createUser.xm").as("createUser.xm"))
                        .add(Property.forName("shr.xm").as("shr.xm"))
                        .add(Property.forName("zt").as("zt"))
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("shrq").as("shrq")))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if(gsid!=null){
            if(bmid!=null)
                criteria.add(Restrictions.and(Restrictions.eq("gs.id", gsid),Restrictions.eq("bm.id", bmid)));
            else
                criteria.add(Restrictions.eq("gs.id", gsid));
        }
        if(nf!=null)
            criteria.add(Restrictions.eq("nf", nf));
        if(yf != null)
            criteria.add(Restrictions.eq("yf", yf));
        if(StringUtils.isNotEmpty(x))
            criteria.add(Restrictions.eq("x", x));
//        RegionDao.createDataRegion(criteria);
        Page list = this.findByCriteria(page, criteria);
        return list;
    }

    public List<Map> findItems(Boolean isGs,Boolean isBm){
        String where = "";
        List<Object> param = new ArrayList<Object>();
        if(isGs){
            where += " isGs = ?";
            param.add(isGs);
        }
        if(isBm){
            if(where.length()!=0)
                where += " and ";
            where += "isBm = ?";
            param.add(isBm);
        }
        String hql = "select new map(id as id,itemName as ItemName,spacecnt as spacecnt) from GsjyysItem"+(where.length()!=0?" where ":"") + where;
        return find(hql,param.toArray());
    }

    public Boolean existGsjyys(Gsjyys gsjyys){
        StringBuilder condition = new StringBuilder();
        List<Object> param = new ArrayList<Object>();
        condition.append(" gs = ? and nf = ? and yf = ? and x = ? and zt = ?");
        param.add(gsjyys.getGs());
        param.add(gsjyys.getNf());
        param.add(gsjyys.getYf());
        param.add(gsjyys.getX());
        param.add("ybc");
        if(gsjyys.getBm()!=null){
            condition.append("and bm = ?");
            param.add(gsjyys.getBm());
        }
        String hql = "from Gsjyys" + (condition.length() != 0 ? " where " : "") + condition.toString();
        return existsQuery(hql,param.toArray());
    }

    public List<Map> findGsJc(){
        String hql = "select distinct new map(gs.jc as jc,gs.zjm as zjm) from Gsjyys";
        return find(hql);
    }

    public List<Map> findHz(Integer nf,Integer yf,String x){
//        StringBuilder where = new StringBuilder();
//        if(nf!=null){
//            if(where.length()!=0)
//                where.append(" and ");
//            where.append("nf = :nf");
//        }
//        if(yf != null){
//            if(where.length()!=0)
//                where.append(" and ");
//            where.append("yf = :yf");
//        }
//        if(x != null){
//            if(where.length()!=0)
//                where.append(" and ");
//            where.append("x = :x");
//        }
        String sql = "select t.mc as \"itemName\", wmsys.wm_concat(t.jgmc || '@' || t.jgzjm || '@' || t.je || '') as \"hz\"" +
                "  from (select (select itemname from t_biz_gsjyysitem where id = item_id) mc,je," +
                "  (select jc from t_sys_gsjg where id = gs_id) jgmc," +
                "  (select zjm from t_sys_gsjg where id = gs_id) jgzjm from (select a.*, b.gs_id" +
                "  from t_biz_gsjyysmx a, " +
                "  (select id, gs_id from t_biz_gsjyys where nf = :nf and yf = :yf and x = :x and deleted = 0 and zt = 'ysh') b" +
                "  where a.gsjyys_id = b.id)) t group by t.mc";
        SQLQuery sqlQuery = getSession().createSQLQuery(sql);
        sqlQuery.setInteger("nf",nf).setInteger("yf",yf).setString("x",x).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return sqlQuery.list();
    }
}
